package del4;
import java.sql.*;

public class StatementTest {
	static final String dbName = "PerformanceTest";
	static final String url = "jdbc:derby:" + dbName + ";create=true"; //opret databasen hvis den ikke findes
	static Connection con = null;
	
	private static void prepDB() throws SQLException{
		Statement stmt = con.createStatement();
		// vi sletter tabellen hvis den allerede findes
		String sql = "DROP TABLE Person ";
		try {
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// vi ignorerer bevidst denne exception
		}
		// opret person-tabellen
		sql = "CREATE TABLE Person ";
		sql += "(person_pk INTEGER NOT NULL PRIMARY KEY, name VARCHAR(64))";
		stmt.executeUpdate(sql);
		stmt.close();
	}
	
	public static void main(String[] args) throws Exception {
		Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
		con = DriverManager.getConnection(url);
		//Reset the DB.
		prepDB();
		
		ResultSet res = null;
		long totalTimeStatementInsert = 0;
		long totalTimeStatementSelect = 0;
		long totalTimePreparedStatementInsert = 0;
		long totalTimePreparedStatementSelect = 0;
		long timeBefore = 0;
		
		//Test insert with statement
		Statement stmt = con.createStatement();
		timeBefore = System.currentTimeMillis();
		for(int i = 0; i < 1000; i++){
			stmt.executeUpdate(
					"INSERT INTO Person " +
					"(person_pk, name) " +
					"VALUES (" + (i + 1) + ", 'Test " + i + "')"
			);
		}
		totalTimeStatementInsert = System.currentTimeMillis() - timeBefore;
		
		//Test select with statement
		timeBefore = System.currentTimeMillis();
		for(int i = 0; i < 1000; i++){
			res = stmt.executeQuery(
					"SELECT name FROM Person WHERE person_pk = " + (i + 1)
			);
			while(res.next()){
				//Just run through them...
			}
		}
		totalTimeStatementSelect = System.currentTimeMillis() - timeBefore;
		stmt.close();
		
		//Reset the DB.
		prepDB();
		
		//Test insert with prepared statement
		PreparedStatement pstmt = con.prepareStatement(
				"INSERT INTO Person " +
				"(person_pk, name) " +
				"VALUES (?, ?)"
		);
		timeBefore = System.currentTimeMillis();
		for(int i = 0; i < 1000; i++){
			pstmt.setInt(1, i);
			pstmt.setString(2, "Test" + i);
			pstmt.executeUpdate();
		}
		totalTimePreparedStatementInsert = System.currentTimeMillis() - timeBefore;
		pstmt.close();
		
		//Test select with prepared statement
		pstmt = con.prepareStatement(
				"SELECT name FROM Person WHERE person_pk = ?"
		);
		timeBefore = System.currentTimeMillis();
		for(int i = 0; i < 1000; i++){
			pstmt.setInt(1, i + 1);
			res = pstmt.executeQuery();
			while(res.next()){
				//Just run through them...
			}
		}
		totalTimePreparedStatementSelect = System.currentTimeMillis() - timeBefore;
		pstmt.close();
		
		System.out.println("Time taken to do 1000 inserts:");
		System.out.println("Statement: " + totalTimeStatementInsert);
		System.out.println("Prepared Statement: " + totalTimePreparedStatementInsert);
		System.out.println();
		System.out.println("Time taken to do 1000 selects:");
		System.out.println("Statement: " + totalTimeStatementSelect);
		System.out.println("Prepared Statement: " + totalTimePreparedStatementSelect);
		
		/*
		 * Test results...
		 * Time taken to do 1000 inserts:
		 * Statement: 11204
		 * Prepared Statement: 3042
		 * 
		 * Time taken to do 1000 selects:
		 * Statement: 3340
		 * Prepared Statement: 315
		 * 
		 * We suppose the prepared statement is faster because the statement has been made
		 * and therefore it doesn't have to be created again.
		 * Also we're simply setting to variables when using the prepared statement,
		 * while we initialize a new string every time we create the statement.
		 * 
		 * (4.2)
		 * We suppose that if we have to set a loooooooot of parameters then it might be faster
		 * to use the statement as everything is being set at the time of creation..
		 * More importantly it should probably be a query we would only execute once.
		 */
		con.close();
	}

}
